//
//  MessageDataBaseModel.m
//  ExcellentHome
//
//  Created by 揭婧雯 on 16/8/3.
//  Copyright © 2016年 陈区. All rights reserved.
//

#import "MessageDataBaseModel.h"
#import "MessageModel.h"

#define KDB_NAME @"ExcellentHome"
#define KMESSAGETABLENAME @"Message_Table_New"//4.3.7新表
#define KISMESSAGEDATADELETE @"isMessageOldDataDelete"

@implementation MessageDataBaseModel
/**
 *  单例统一管理Message涉及数据库操作
 */
+ (instancetype)sharedDataBaseModel
{
    static MessageDataBaseModel* instance;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        instance = [[self alloc] init];
    });

    return instance;
}

- (instancetype)init
{
    self = [super init];
    if (self) {
        //处理以前版本的老数据
        NSUserDefaults* userDefault = [NSUserDefaults standardUserDefaults];
        NSString* isOldDataDelete = [userDefault objectForKey:KISMESSAGEDATADELETE];
        if (!isOldDataDelete || ![isOldDataDelete isEqualToString:@"1"]) {
            NSString* sqlString = [NSString stringWithFormat:@"DELETE FROM %@", @"Message_Table"];
            [SEGBasicDataBase executeDB:KDB_NAME WithSQL:sqlString OfValues:@[]];
            //成功后 设置标记
            [userDefault setObject:@"1" forKey:KISMESSAGEDATADELETE];
        }
        
        // 创建数据库
        NSString* realSql = @"CREATE TABLE IF NOT EXISTS 'Message_Table_New'('id' integer primary key autoincrement,messageId Text,userid Text,usertype Text,title Text,message Text,objectid Text,groupid Text,sender Text,usericon Text,businesstype Text,type Text,createTime Text,isRead Text,uuid Text,isTop Text,contenttype Text,sendUserId Text,sendUserType Text,turnTip Text,turnType Text,communityId Text,submsg Text,lable Text)";
        BOOL ss = [SEGBasicDataBase createTableWithDBName:KDB_NAME WithTable:realSql];
    }
    return self;
}


/**
 *  保存消息到数据库 
 *
 *  @param dict 消息字典
 *  @return 保存结果,只有插入失败返回NO，其他情况返回Yes
 */
- (BOOL)saveMessageWithDictionary:(NSDictionary*)dict
{
    //先转成模型使用，防止数据库返回多余数据
    MessageModel *model = [MessageModel initWithDictionary:dict];
    //借条不存数据库 防止服务器错误返回
    if ([model.contenttype isEqualToString:@"5"]) {
        return YES;
    }
    
    //过滤掉非我相关的点赞、评论、系统消息
    if ([model.groupid isEqualToString:kMessageGroupLikePre] ||
        [model.groupid isEqualToString:kMessageGroupCommentPre] ||
        [model.groupid isEqualToString:kMessageGroupSystemPre]
        ) {
        NSString *suffix = [NSString stringWithFormat:@"|%@",kAccountUserID];
        if (![model.groupid hasSuffix:suffix]) {//不是自己的 不保存数据
            return YES;
        }
    }
    
    //物业服务号 数据过滤
    if ([model.groupid hasPrefix:kMessageGroupServicePre]) {
        NSString *suffix = [NSString stringWithFormat:@"|%@",kAccountUserID];
        if (![model.groupid hasSuffix:suffix]) {
            if ([model.groupid rangeOfString:@"|"].location == NSNotFound) {
                //拼上自己的userID
                model.groupid = [NSString stringWithFormat:@"%@%@",model.groupid, suffix];
            }else {
                //包含其他人的userID 不保存数据
                return YES;
            }
        }
    }
    
    //邻居不保存自己发送给自己的消息
    if ([model.businesstype isEqualToString:@"1"] && [model.sendUserId isEqualToString:kAccountUserID]) {
        return YES;
    }
    //私信不保存自己发送给自己的消息
    if ([model.businesstype isEqualToString:@"2"] && [model.sendUserId isEqualToString:kAccountUserID]) {
        return YES;
    }
    
    //判断缓存中是否存在相同的消息，如果不存在就添加消息
    NSString* messageId = model.messageId;
    NSString* sqlString = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE messageId = '%@' AND uuid = '%@' AND communityId = '%@'", KMESSAGETABLENAME, messageId, kAccountUserID, kAccountCommunityID];
    NSArray* result = [SEGBasicDataBase executeQueryWithDBName:KDB_NAME WithSQL:sqlString OfValues:@[]];

    if (result.count <= 0) {
        NSString* type = model.type;
        if ([type isEqualToString:kMessageTypeUserFeedbackReply]) { //用户反馈回复 同一objectid只保留一条（删除其他）
            NSString* sqlString1 = [NSString stringWithFormat:@"DELETE FROM %@ WHERE objectid = ?", KMESSAGETABLENAME];
            [SEGBasicDataBase executeDB:KDB_NAME WithSQL:sqlString1 OfValues:@[model.objectid]];
        }

        // 拼数据
        NSMutableDictionary* params = [NSMutableDictionary dictionaryWithDictionary:[model getDictionaryFromModel]];
        [params setObject:kAccountUserID forKey:@"uuid"]; //登陆用户id
        if ([model.sendUserId isEqualToString:kAccountUserID]) { //自己发送的信息
            [params setObject:@"1" forKey:@"isRead"];
            [params setObject:@"0" forKey:@"sendUserType"];
        }
        else {
            [params setObject:@"0" forKey:@"isRead"];
            [params setObject:@"1" forKey:@"sendUserType"];
        }
        //服务器没有返回字段 本地存小区ID
        [params setObject:kAccountCommunityID forKey:@"communityId"];

        NSMutableString* keys = [NSMutableString string];
        NSMutableString* values = [NSMutableString string];
        NSMutableArray* valuesArr = [NSMutableArray array];
        for (int i = 0; i < params.count; i++) {
            NSString* key = params.allKeys[i];
            NSString* value = params.allValues[i];
            [keys appendFormat:@"%@,", key];
            [values appendString:@"?,"];
            [valuesArr addObject:value];
        }

        // 插入数据库
        sqlString = [NSString stringWithFormat:@"INSERT INTO %@(%@) VALUES(%@)", KMESSAGETABLENAME, [keys substringToIndex:keys.length - 1], [values substringToIndex:values.length - 1]];
        BOOL ss = [SEGBasicDataBase executeDB:KDB_NAME WithSQL:sqlString OfValues:valuesArr];
        //如果失败重新插入，最多十次
        NSInteger max = 10;
        while (ss == NO && max > 0) {
            ss = [SEGBasicDataBase executeDB:KDB_NAME WithSQL:sqlString OfValues:valuesArr];
            max--;
        }
        
        return ss;
    }
    return YES;
}

/**
 *  读取消息中心列表要显示的消息
 *
 *  @return 查询结果
 */
- (NSArray*)getMessageRootVCListData
{
    NSString* sqlString = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE uuid = %@ AND communityId = %@ AND messageId <> '' ORDER BY isTop DESC, createTime DESC", KMESSAGETABLENAME ,kAccountUserID, kAccountCommunityID]; //防止查到自己发送的信息(本地消息)messageId <> ''
    NSArray* resultArray = [SEGBasicDataBase executeQueryWithDBName:KDB_NAME WithSQL:sqlString OfValues:@[]];
    
    NSArray *totalArray = [self getTotalArrayWithDataBaseResultArray:resultArray conditionDict:@{}];
    //按isTop 降序
    NSArray *sortArray = [totalArray sortedArrayUsingComparator:^NSComparisonResult(MessageModel *obj1, MessageModel *obj2) {
        return [obj2.isTop compare:obj1.isTop];
    }];
    NSArray *sortArray2 = [sortArray sortedArrayUsingComparator:^NSComparisonResult(MessageModel *obj1, MessageModel *obj2) {
        return [obj2.createTime compare:obj1.createTime];
    }];
    return sortArray2;
}

/**
 *  获取指定会话的消息 获取指定会话的消息 私聊用 时间升序排列，最老的在最前面
 *
 *  @param groupId 查询参数
 *
 *  @return 查询结果数组
 */
- (NSArray*)getMessageWithGroupId:(NSString*)groupId
{
    NSString* sqlString = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE uuid = %@ AND communityId = %@ AND groupid = ? ORDER BY createTime ASC", KMESSAGETABLENAME, kAccountUserID, kAccountCommunityID];
    NSArray* resultArray = [SEGBasicDataBase executeQueryWithDBName:KDB_NAME WithSQL:sqlString OfValues:@[ groupId ]];
    
    NSArray *totalArray = [self getTotalArrayWithDataBaseResultArray:resultArray conditionDict:@{@"groupid" : groupId}];
    //按createTime 升序
    NSArray *sortArray = [totalArray sortedArrayUsingComparator:^NSComparisonResult(MessageModel *obj1, MessageModel *obj2) {
        return [obj1.createTime compare:obj2.createTime];
    }];
    return sortArray;
}

/**
 *  获取指定groupId的消息 降序排列，最新的在最前面
 *
 *  @param groupId 查询参数
 *
 *  @return 查询结果数组
 */
- (NSArray*)getMessagesDescendingWithGroupId:(NSString*)groupId
{
    NSString* sqlString = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE uuid = %@ AND communityId = %@ AND groupid = ? ORDER BY createTime DESC", KMESSAGETABLENAME, kAccountUserID, kAccountCommunityID];
    NSArray* resultArray = [SEGBasicDataBase executeQueryWithDBName:KDB_NAME WithSQL:sqlString OfValues:@[ groupId ]];
//    NSArray* modelArray = [self seg_changeDictArrayToModelArray:resultArray];
//    return modelArray;
    NSArray *totalArray = [self getTotalArrayWithDataBaseResultArray:resultArray conditionDict:@{@"groupid" : groupId}];
    //按createTime 降序
    NSArray *sortArray = [totalArray sortedArrayUsingComparator:^NSComparisonResult(MessageModel *obj1, MessageModel *obj2) {
        return [obj2.createTime compare:obj1.createTime];
    }];
    return sortArray;
}

/**
 *  获取每个群组中的未读消息 可替换[[BusinessEntityManager sharedManager] NewPublicEntityManager] onGroupMessage:
 *
 *  @param dict  查询条件数组
 *
 *  @return 查询结果数组
 */
- (NSArray*)getGroupMessageWithDictionary:(NSDictionary*)dict
{
    NSMutableString* condition = [NSMutableString stringWithFormat:@"uuid = %@ AND communityId = %@ AND isRead = %@", kAccountUserID, kAccountCommunityID, @"0"];
    if ([dict objectForKey:@"groupid"]) {
        [condition appendFormat:@" AND groupid = %@", [dict objectForKey:@"groupid"]];
    }
    else if ([dict objectForKey:@"objectid"]) {
        [condition appendFormat:@" AND objectid = %@", [dict objectForKey:@"objectid"]];
    }

    NSString* sqlString = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ ORDER BY 'createTime' DESC", KMESSAGETABLENAME, condition];
    NSArray* resultArray = [SEGBasicDataBase executeQueryWithDBName:KDB_NAME WithSQL:sqlString OfValues:@[]];

//    NSArray* modelArray = [self seg_changeDictArrayToModelArray:resultArray];
//    return modelArray;
    NSArray *totalArray = [self getTotalArrayWithDataBaseResultArray:resultArray conditionDict:dict];
    //按createTime 降序
    NSArray *sortArray = [totalArray sortedArrayUsingComparator:^NSComparisonResult(MessageModel *obj1, MessageModel *obj2) {
        return [obj2.createTime compare:obj1.createTime];
    }];
    return sortArray;
}

///**
// *  更新数据库中的消息的是否已读状态
// *
// *  @param array 需要更新的消息模型数组
// */
//- (void)updateMessagesIsReadInDataBase:(NSArray*)modelArray
//{
//    NSUInteger count = modelArray.count;
//    for (int i = 0; i < count; i++) {
//        MessageModel* message = modelArray[i];
//        NSString* sqlString = [NSString stringWithFormat:@"UPDATE %@ SET isRead = ? WHERE messageId = ?", KMESSAGETABLENAME];
//        [SEGBasicDataBase executeDB:KDB_NAME WithSQL:sqlString OfValues:@[message.isRead, message.messageId]];
//    }
//    
//}

/**
 *  更新数据库中的所有相同groupId的消息为已读状态
 *
 *  @param groupId 需要更新的消息groupId
 */
- (void)updateMessagesToReadInDataBaseWithGroupId:(NSString *)groupId
{
    NSString* sqlString = [NSString stringWithFormat:@"UPDATE %@ SET isRead = ? WHERE groupid = ? AND isRead = ?", KMESSAGETABLENAME];
    [SEGBasicDataBase executeDB:KDB_NAME WithSQL:sqlString OfValues:@[@"1", groupId, @"0"]];
    
    NSMutableArray *archiveArray = [NSMutableArray arrayWithArray:[self getArchiveMessageArray]];
    if (archiveArray.count > 0) {
        [archiveArray enumerateObjectsUsingBlock:^(NSDictionary *obj, NSUInteger idx, BOOL * _Nonnull stop) {
            if ([obj[@"groupid"] isEqualToString: groupId]) {
                NSMutableDictionary *tempDict = [NSMutableDictionary dictionaryWithDictionary:obj];
                tempDict[@"isRead"] = @"1";//设置为已读
                [archiveArray replaceObjectAtIndex:idx withObject:tempDict];
            }
        }];
        [self archiveMessageArray:archiveArray];
    }
}

/**
 *  保存消息Model到数据库 直接保存 没有过滤
 *
 *  @param dict 消息字典
 */
- (BOOL)saveMessageWithModel:(MessageModel*)model
{
    NSDictionary* params = [model getDictionaryFromModel];

    NSMutableString* keys = [NSMutableString string];
    NSMutableString* values = [NSMutableString string];
    NSMutableArray* valuesArr = [NSMutableArray array];
    for (int i = 0; i < params.count; i++) {
        NSString* key = params.allKeys[i];
        NSString* value = params.allValues[i];
        [keys appendFormat:@"%@,", key];
        [values appendString:@"?,"];
        [valuesArr addObject:value];
    }

    // 插入数据库
    NSString* sqlString = [NSString stringWithFormat:@"INSERT INTO %@(%@) VALUES(%@)", KMESSAGETABLENAME, [keys substringToIndex:keys.length - 1], [values substringToIndex:values.length - 1]];
    BOOL ss = [SEGBasicDataBase executeDB:KDB_NAME WithSQL:sqlString OfValues:valuesArr];
    return ss;
}

/**
 *  转换字典数组为模型数组
 *
 *  @param dictArray 字典数组
 *
 *  @return 模型数组
 */
- (NSArray*)seg_changeDictArrayToModelArray:(NSArray*)dictArray
{
    NSMutableArray* modelArray = [[NSMutableArray alloc] init];
    NSUInteger count = dictArray.count;
    for (int i = 0; i < count; i++) {
        MessageModel* messageModel = [MessageModel initWithDictionary:dictArray[i]];
        [modelArray addObject:messageModel];
    }
    
    //按时间排序 降序 最新的在最前面
//    NSArray *sortArray = [modelArray sortedArrayUsingComparator:^NSComparisonResult(MessageModel *obj1, MessageModel *obj2) {
//        return [obj2.createTime compare:obj1.createTime];
//    }];
    
    return modelArray;
}

//获取数据库中所有的model
- (NSArray*)getAllDataInDataBase
{
    NSString* sqlString = [NSString stringWithFormat:@"SELECT * FROM %@", KMESSAGETABLENAME];
    NSArray* result = [SEGBasicDataBase executeQueryWithDBName:KDB_NAME WithSQL:sqlString OfValues:@[]];
    NSArray* modelArray = [self seg_changeDictArrayToModelArray:result];
    return modelArray;
}

/**
 *  统计数据库中的消息 可替换[[BusinessEntityManager sharedManager] NewPublicEntityManager] onMessageEntityCountRecord:
 *
 *  @param data
 *
 *  @return 符合条件的数据条数
 */
//- (NSInteger)getMessageCountRecordWithBusinesstype:(NSString*)businesstype
//{
//    NSMutableString* condition = [NSMutableString stringWithFormat:@"uuid = %@ AND isRead = %@", kAccountUserID, @"0"];
//    if ([businesstype isEqualToString:@"1"] || [businesstype isEqualToString:@"2"] || [businesstype isEqualToString:@"3"]) {
//        [condition appendFormat:@" AND businesstype = %@", businesstype];
//    }
//
//    NSString* sqlString = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ ORDER BY 'isTop' DESC, 'createTime' DESC", KMESSAGETABLENAME, condition];
//    NSArray* resultArray = [SEGBasicDataBase executeQueryWithDBName:KDB_NAME WithSQL:sqlString OfValues:@[]];
//
//    return resultArray.count;
//}

- (NSArray *)getTotalArrayWithDataBaseResultArray:(NSArray *)resultArray conditionDict:(NSDictionary *)conditions
{
    NSMutableArray *totalArray = [NSMutableArray arrayWithArray:resultArray];
    NSArray *archiveArray = [self getArchiveMessageArray];
    
    [archiveArray enumerateObjectsUsingBlock:^(NSDictionary *obj, NSUInteger idx, BOOL * _Nonnull stop) {
        //是否已存在
        __block BOOL isContain = NO;
        [totalArray enumerateObjectsUsingBlock:^(NSDictionary *obj1, NSUInteger idx, BOOL * _Nonnull stop) {
            if ([obj[@"messageId"] isEqualToString: obj1[@"messageId"]]) {
                isContain = YES;
                *stop = YES;
            }
        }];
        
        //是否符合条件
        __block BOOL flag = YES;
        if (conditions) {
            [conditions enumerateKeysAndObjectsUsingBlock:^(id  _Nonnull key, id  _Nonnull value, BOOL * _Nonnull stop) {
                if (![obj[key] isEqualToString:value]) {
                    flag = NO;
                    *stop = YES;
                }
            }];
        }
        
        if (!isContain && flag) {
            [totalArray addObject:obj];
        }
    }];
    
//    [self saveArchiveArraysToDataBase:archiveArray];
    NSArray* modelArray = [self seg_changeDictArrayToModelArray:totalArray];
    return modelArray;
}

- (void)saveArchiveArraysToDataBase:(NSArray *)archiveArray
{
    //有未插入数据库的数据，在子线程中插入数据库
    if(archiveArray.count > 0) {
        dispatch_async(dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_BACKGROUND, 0), ^{
            NSMutableArray *failedArr = [NSMutableArray array];
            for (NSDictionary* dictt in archiveArray) {
                //保存消息
                BOOL ss = [self saveMessageWithDictionary:dictt];
                if(!ss) {//多次重复插入还没有成功 保存起来
                    [failedArr addObject:dictt];
                }
            }
            //归档
            [self archiveMessageArray:failedArr];
        });
    }
}

- (NSArray*)getArchiveMessageArray
{
    //解档
    NSArray *arr = [NSKeyedUnarchiver unarchiveObjectWithFile:[self segMessageLocalPath]];
//    NSLog(@"getArchiveMessageArray = %@",[arr description]);
    return arr;
}

- (BOOL)archiveMessageArray:(NSArray *)array
{
    //归档
    BOOL flag = [NSKeyedArchiver archiveRootObject:array toFile:[self segMessageLocalPath]];
//    NSLog(@"flag = %d",flag);
    return flag;
}

- (NSString*)segMessageLocalPath
{
    NSString *filePath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
    return [filePath stringByAppendingPathComponent:[NSString stringWithFormat:@"SEGArchiverMessage_%@_%@.data",kAccountUserID,kAccountCommunityID]];
}


@end
